﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_SearchEnterprises')
BEGIN
	DROP  Procedure  esf_sso.P_SearchEnterprises
END
GO

CREATE Procedure esf_sso.P_SearchEnterprises
	@pID						BIGINT = NULL
	,@pName						VARCHAR (60) = NULL
	,@pIdentificationTypeID		BIGINT = NULL
	,@pIdentificationNumber		VARCHAR (20) = NULL
	,@pPageIndex				BIGINT = 0
	,@pPageSize					BIGINT = 30
AS

	SELECT * FROM
		(SELECT
			ROW_NUMBER () OVER (ORDER BY VE.Ent_Name) AS Ri
			,COUNT(*) OVER () AS RCount
			,VE.*
		FROM
			esf_sso.VEnterprise VE
		WHERE
			(@pID IS NULL OR VE.Ent_ID = @pID)
			AND (@pName IS NULL OR VE.Ent_Name LIKE @pName)
			AND (@pIdentificationTypeID IS NULL OR VE.Ent_IdType_ID = @pIdentificationTypeID)
			AND (@pIdentificationNumber IS NULL OR VE.Ent_IdentificationNumber = @pIdentificationNumber)
		) T
	WHERE
		T.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
	ORDER BY 
		T.Ri

GO